<?php

static $registry = NULL;

// Error Handler
function error_handler_for_export($errno, $errstr, $errfile, $errline) {
	global $registry;
	
	switch ($errno) {
		case E_NOTICE:
		case E_USER_NOTICE:
			$errors = "Notice";
			break;
		case E_WARNING:
		case E_USER_WARNING:
			$errors = "Warning";
			break;
		case E_ERROR:
		case E_USER_ERROR:
			$errors = "Fatal Error";
			break;
		default:
			$errors = "Unknown";
			break;
	}
	
	$config = $registry->get('config');
	$url = $registry->get('url');
	$request = $registry->get('request');
	$session = $registry->get('session');
	$log = $registry->get('log');
	
	if ($config->get('config_error_log')) {
		$log->write('PHP ' . $errors . ':  ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
	}

	if (($errors=='Warning') || ($errors=='Unknown')) {
		return true;
	}

	if (($errors != "Fatal Error") && isset($request->get['route']) && ($request->get['route']!='tool/export/download'))  {
		if ($config->get('config_error_display')) {
			echo '<b>' . $errors . '</b>: ' . $errstr . ' in <b>' . $errfile . '</b> on line <b>' . $errline . '</b>';
		}
	} else {
		$session->data['export_error'] = array( 'errstr'=>$errstr, 'errno'=>$errno, 'errfile'=>$errfile, 'errline'=>$errline );
		$token = $request->get['token'];
		$link = $url->link( 'tool/export', 'token='.$token, 'SSL' );
		header('Status: ' . 302);
		header('Location: ' . str_replace(array('&amp;', "\n", "\r"), array('&', '', ''), $link));
		exit();
	}

	return true;
}


function fatal_error_shutdown_handler_for_export()
{
	$last_error = error_get_last();
	if ($last_error['type'] === E_ERROR) {
		// fatal error
		error_handler_for_export(E_ERROR, $last_error['message'], $last_error['file'], $last_error['line']);
	}
}


class ModelToolExport extends Model {

	private $error = array();

	function clean( &$str, $allowBlanks=FALSE ) {
		$result = "";
		$n = strlen( $str );
		for ($m=0; $m<$n; $m++) {
			$ch = substr( $str, $m, 1 );
			if (($ch==" ") && (!$allowBlanks) || ($ch=="\n") || ($ch=="\r") || ($ch=="\t") || ($ch=="\0") || ($ch=="\x0B")) {
				continue;
			}
			$result .= $ch;
		}
		return $result;
	}


	function multiquery( &$database, $sql ) {
		foreach (explode(";\n", $sql) as $sql) {
			$sql = trim($sql);
			if ($sql) {
				$database->query($sql);
			}
		}
	}


	protected function getDefaultLanguageId( &$database ) {
		$code = $this->config->get('config_language');
		$sql = "SELECT language_id FROM `".DB_PREFIX."language` WHERE code = '$code'";
		$result = $database->query( $sql );
		$languageId = 1;
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$languageId = $row['language_id'];
				break;
			}
		}
		return $languageId;
	}


	protected function getDefaultWeightUnit() {
		$weightUnit = $this->config->get( 'config_weight_class' );
		return $weightUnit;
	}


	protected function getDefaultMeasurementUnit() {
		$measurementUnit = $this->config->get( 'config_length_class' );
		return $measurementUnit;
	}


	function getWeightClassIds( &$database ) {
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// find all weight classes already stored in the database
		$weightClassIds = array();
		$sql = "SELECT `weight_class_id`, `unit` FROM `".DB_PREFIX."weight_class_description` WHERE `language_id`=$languageId;";
		$result = $database->query( $sql );
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$weightClassId = $row['weight_class_id'];
				$unit = $row['unit'];
				if (!isset($weightClassIds[$unit])) {
					$weightClassIds[$unit] = $weightClassId;
				}
			}
		}

		return $weightClassIds;
	}


	function getLengthClassIds( &$database ) {
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// find all length classes already stored in the database
		$lengthClassIds = array();
		$sql = "SELECT `length_class_id`, `unit` FROM `".DB_PREFIX."length_class_description` WHERE `language_id`=$languageId;";
		$result = $database->query( $sql );
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$lengthClassId = $row['length_class_id'];
				$unit = $row['unit'];
				if (!isset($lengthClassIds[$unit])) {
					$lengthClassIds[$unit] = $lengthClassId;
				}
			}
		}

		return $lengthClassIds;
	}


	protected function getAvailableStoreIds( &$database ) {
		$sql = "SELECT store_id FROM `".DB_PREFIX."store`;";
		$result = $database->query( $sql );
		$storeIds = array(0);
		foreach ($result->rows as $row) {
			if (!in_array((int)$row['store_id'],$storeIds)) {
				$storeIds[] = (int)$row['store_id'];
			}
		}
		return $storeIds;
	}

	protected function detect_encoding( $str ) {
		// auto detect the character encoding of a string
		return mb_detect_encoding( $str, 'UTF-8,ISO-8859-15,ISO-8859-1,cp1251,KOI8-R' );
	}


	// hook function for reading additional cells in class extensions
	protected function moreItemCells( $i, &$j, &$worksheet, $item ) {
		return $item;
	}

	protected function morePurchasePriceCells( $i, &$j, &$worksheet, $purchase ) {
		return $purchase;
	}

	protected function moreSalePriceCells( $i, &$j, &$worksheet, $saleprice ) {
		return $saleprice;
	}

	protected function moreSupplierCells( $i, &$j, &$worksheet, $supplier ) {
		return $supplier;
	}

	//cac ham cua item
	function storeItemsIntoDatabase( &$database, &$items ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove item
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."fa_item`;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."fa_item_description` WHERE language_id=$languageId;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."url_alias` WHERE `query` LIKE 'item_id=%';\n";

		$this->multiquery( $database, $sql );
		
		// get pre-defined store_ids
		$availableStoreIds = $this->getAvailableStoreIds( $database );
		
		
		// get weight classes
		$weightClassIds = $this->getWeightClassIds( $database );
		
		// get length classes
		$lengthClassIds = $this->getLengthClassIds( $database );
		
		// generate and execute SQL for storing the items
		foreach ($items as $item) {
			$itemId = $item['item_id'];
			$itemName = $database->escape($item['name']);
			$group_id = $item['group_id'];
			$purchase_code = $item['purchase_code'];
			$sale_code = $item['sale_code'];
			$unit = $item['unit'];
			$image = $item['image'];
			$status = $item['status'];
			$status = ((strtoupper($status)=="TRUE") || (strtoupper($status)=="YES") || (strtoupper($status)=="ENABLED")) ? 1 : 0;
			$itemDescription = $database->escape($item['description']);
			$sql  = "INSERT INTO `".DB_PREFIX."fa_item` (`item_id`, `group_id`, `purchase_code`, `sale_code`, `unit`, `image`, `status`) VALUES ";
			$sql .= "('$itemId', '$group_id', '$purchase_code', '$sale_code', '$unit', '$image', '$status');";
			$sql2 = "INSERT INTO `".DB_PREFIX."fa_item_description`(`item_id`, `language_id`, `name`, `description`) VALUES ";
			$sql2 .= "($itemId,$languageId,'$itemName','$itemDescription');";
			
			$database->query($sql);
			$database->query($sql2);
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}

	function uploadItems( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);		
		
		$data = $reader->getSheet(0);
		$items = array();
		$item = array();
		$isFirstRow = TRUE;
		$i = 0;
		$k = $data->getHighestRow();
		for ($i=0; $i<$k; $i+=1) {
			$j = 1;
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$itemId = trim($this->getCell($data,$i,$j++));
			if ($itemId=="") {
				continue;
			}
			$name = $this->getCell($data,$i,$j++);
			$name = htmlentities( $name, ENT_QUOTES, $this->detect_encoding($name) );
			
			$group_id = $this->getCell($data,$i,$j++);
			$purchase_code = $this->getCell($data,$i,$j++);
			$sale_code = $this->getCell($data,$i,$j++);
			
			$image = $this->getCell($data,$i,$j++);
			
			$unit = $this->getCell($data,$i,$j++);
			
			$status = $this->getCell($data,$i,$j++,'true');
			$langId = $this->getCell($data,$i,$j++,'1');
			if ($langId!=$languageId) {
				continue;
			}
			$description = $this->getCell($data,$i,$j++);
			$description = htmlentities( $description, ENT_QUOTES, $this->detect_encoding($description) );
			
			$item = array();
			$item['item_id'] = $itemId;
			$item['name'] = $name;
			$item['group_id'] = $group_id;
			$item['purchase_code'] = $purchase_code;
			$item['sale_code'] = $sale_code;
			$item['image'] = $image;
			$item['unit'] = $unit;
			$item['status'] = $status;
			$item['language_id'] = $languageId;
			$item['description'] = $description;
			
			$items[$itemId] = $this->moreItemCells( $i, $j, $data, $item );
		}
		return $this->storeItemsIntoDatabase( $database, $items );
	}

	protected function expectedItemsHeading() {
		return array( "item_id", "name", "group_id", "purchase_code", "sale_code", "image", "unit","status", "language_id", "description");
	}

	function validateItems( &$reader )
	{
		$expectedItemsHeading = $this->expectedItemsHeading();
		$data =& $reader->getSheet(0);
		return $this->validateHeading( $data, $expectedItemsHeading );
	}

	protected function getItems( &$database, $languageId ) {
		$query  = "SELECT *";
		$query .= "FROM `".DB_PREFIX."fa_item` p ";
		$query .= "LEFT JOIN `".DB_PREFIX."fa_item_description` pd ON p.item_id=pd.item_id ";
		$query .= "  AND pd.language_id=$languageId ";
		$query .= "GROUP BY p.item_id ";
		$query .= "ORDER BY p.item_id; ";
		$result = $database->query( $query );
		return $result->rows;
	}
	//het cac ham item

	//cac ham purchase price

	function storePurchasePirceIntoDatabase( &$database, &$purchases ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove item
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."fa_purchase_price`;\n";

		$this->multiquery( $database, $sql );
		
		// get pre-defined store_ids
		$availableStoreIds = $this->getAvailableStoreIds( $database );
		
		// generate and execute SQL for storing the purchase price
		foreach ($purchases as $purchase) {
			$itemId = $purchase['item_id'];
			$supplierId = $purchase['supplier_id'];
			$price = $purchase['price'];
			$sql  = "INSERT INTO `".DB_PREFIX."fa_purchase_price` (`item_id`, `supplier_id`, `price`) VALUES ";
			$sql .= "('$itemId', '$supplierId', '$price');";
			
			$database->query($sql);
			
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}

	function uploadPurchasePrice( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);		
		
		$data = $reader->getSheet(1);
		$purchases = array();
		$purchase = array();
		$isFirstRow = TRUE;
		$i = 0;
		$k = $data->getHighestRow();
		for ($i=0; $i<$k; $i+=1) {
			$j = 1;
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$itemId = trim($this->getCell($data,$i,$j++));
			if ($itemId=="") {
				continue;
			}
			
			$supplierId = $this->getCell($data,$i,$j++);
			$price = $this->getCell($data,$i,$j++);
			
			$purchase = array();
			$purchase['item_id'] = $itemId;
			$purchase['supplier_id'] = $supplierId;
			$purchase['price'] = $price;
			
			$purchases[$itemId] = $this->morePurchasePriceCells( $i, $j, $data, $purchase );
		}
		return $this->storePurchasePirceIntoDatabase( $database, $purchases );
	}

	protected function expectedPurchasePriceHeading() {
		return array( "item_id", "supplier_id", "price");
	}

	function validatePurchasePrice( &$reader )
	{
		$expectedPurchasePriceHeading = $this->expectedPurchasePriceHeading();
		$data =& $reader->getSheet(1);
		return $this->validateHeading( $data, $expectedPurchasePriceHeading );
	}

	protected function getPurchasePrice( &$database, $languageId ) {
		$query  = "SELECT *";
		$query .= "FROM `".DB_PREFIX."fa_purchase_price` ";
		$query .= "GROUP BY item_id ";
		$query .= "ORDER BY item_id; ";
		$result = $database->query( $query );
		return $result->rows;
	}

	//het purchase price

	//cac ham sale price
	function storeSalePirceIntoDatabase( &$database, &$saleprices ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove item
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."fa_sale_price`;\n";

		$this->multiquery( $database, $sql );
		
		// get pre-defined store_ids
		$availableStoreIds = $this->getAvailableStoreIds( $database );
		
		// generate and execute SQL for storing the purchase price
		foreach ($saleprices as $saleprice) {
			$itemId = $saleprice['item_id'];
			$typeId = $saleprice['type_id'];
			$quantity = $saleprice['quantity'];
			$price = $saleprice['price'];
			$sql  = "INSERT INTO `".DB_PREFIX."fa_sale_price` (`item_id`, `type_id`, `quantity`, `price`) VALUES ";
			$sql .= "('$itemId', '$typeId', '$quantity', '$price');";
			
			$database->query($sql);
			
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}

	function uploadSalePrice( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);		
		
		$data = $reader->getSheet(2);
		$purchases = array();
		$purchase = array();
		$isFirstRow = TRUE;
		$i = 0;
		$k = $data->getHighestRow();
		for ($i=0; $i<$k; $i+=1) {
			$j = 1;
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$itemId = trim($this->getCell($data,$i,$j++));
			if ($itemId=="") {
				continue;
			}
			
			$typeId = $this->getCell($data,$i,$j++);
			$quantity = $this->getCell($data,$i,$j++);
			$price = $this->getCell($data,$i,$j++);
			
			$saleprice = array();
			$saleprice['item_id'] = $itemId;
			$saleprice['type_id'] = $typeId;
			$saleprice['quantity'] = $quantity;
			$saleprice['price'] = $price;
			
			$saleprices[$itemId] = $this->moreSalePriceCells( $i, $j, $data, $saleprice );
		}
		return $this->storeSalePirceIntoDatabase( $database, $saleprices );
	}

	protected function expectedSalePriceHeading() {
		return array( "item_id", "type_id", "quantity", "price");
	}

	function validateSalePrice( &$reader )
	{
		$expectedSalePriceHeading = $this->expectedSalePriceHeading();
		$data =& $reader->getSheet(2);
		return $this->validateHeading( $data, $expectedSalePriceHeading );
	}

	protected function getSalePrice( &$database, $languageId ) {
		$query  = "SELECT *";
		$query .= "FROM `".DB_PREFIX."fa_sale_price` ";
		$query .= "GROUP BY item_id ";
		$query .= "ORDER BY item_id; ";
		$result = $database->query( $query );
		return $result->rows;
	}
	//het sale price

	//supplier
	function storeSupplierIntoDatabase( &$database, &$suppliers ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove item
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `".DB_PREFIX."fa_supplier`;\n";

		$this->multiquery( $database, $sql );
		
		// get pre-defined store_ids
		$availableStoreIds = $this->getAvailableStoreIds( $database );
		
		// generate and execute SQL for storing the supplier
		foreach ($suppliers as $supplier) {
			$supplierId = $supplier['supplier_id'];
			$shortname = $supplier['short_name'];
			$name = $supplier['name'];
			$address = $supplier['address'];
			$taxcode = $supplier['tax_code'];
			$sortorder = $supplier['sort_order'];

			$sql  = "INSERT INTO `".DB_PREFIX."fa_supplier` (`supplier_id`, `short_name`, `name`, `address`, `tax_code`, `sort_order`) VALUES ";
			$sql .= "('$supplierId', '$shortname', '$name', '$address', '$taxcode', '$sortorder');";
			
			$database->query($sql);
			
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}

	function uploadSupplier( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);		
		
		$data = $reader->getSheet(3);
		$suppliers = array();
		$supplier = array();
		$isFirstRow = TRUE;
		$i = 0;
		$k = $data->getHighestRow();
		for ($i=0; $i<$k; $i+=1) {
			$j = 1;
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$supplierId = trim($this->getCell($data,$i,$j++));
			if ($supplierId=="") {
				continue;
			}
			
			$shortname = $this->getCell($data,$i,$j++);
			$name = $this->getCell($data,$i,$j++);
			$address = $this->getCell($data,$i,$j++);
			$tax_code = $this->getCell($data,$i,$j++);
			$sort_order = $this->getCell($data,$i,$j++);
			
			$supplier = array();
			$supplier['supplier_id'] = $supplierId;
			$supplier['short_name'] = $shortname;
			$supplier['name'] = $name;
			$supplier['address'] = $address;
			$supplier['tax_code'] = $tax_code;
			$supplier['sort_order'] = $sort_order;
			
			$suppliers[$supplierId] = $this->moreSalePriceCells( $i, $j, $data, $supplier );
		}
		return $this->storeSupplierIntoDatabase( $database, $suppliers );
	}

	protected function expectedSupplierHeading() {
		return array( "supplier_id", "shortname", "name", "address", "tax_code", "sort_order");
	}

	function validateSupplier( &$reader )
	{
		$expectedSupplierHeading = $this->expectedSupplierHeading();
		$data =& $reader->getSheet(3);
		return $this->validateHeading( $data, $expectedSupplierHeading );
	}

	protected function getSupplier( &$database, $languageId ) {
		$query  = "SELECT *";
		$query .= "FROM `".DB_PREFIX."fa_supplier` ";
		$query .= "GROUP BY supplier_id ";
		$query .= "ORDER BY supplier_id; ";
		$result = $database->query( $query );
		return $result->rows;
	}
	//end supplier
	function getCell(&$worksheet,$row,$col,$default_val='') {
		$col -= 1; // we use 1-based, PHPExcel uses 0-based column index
		$row += 1; // we use 0-based, PHPExcel used 1-based row index
		return ($worksheet->cellExistsByColumnAndRow($col,$row)) ? $worksheet->getCellByColumnAndRow($col,$row)->getValue() : $default_val;
	}


	function validateHeading( &$data, &$expected ) {
		$heading = array();
		$k = PHPExcel_Cell::columnIndexFromString( $data->getHighestColumn() );
		if ($k != count($expected)) {
			return FALSE;
		}
		$i = 0;
		for ($j=1; $j <= $k; $j+=1) {
			$heading[] = $this->getCell($data,$i,$j);
		}
		$valid = TRUE;
		for ($i=0; $i < count($expected); $i+=1) {
			if (!isset($heading[$i])) {
				$valid = FALSE;
				break;
			}
			if (strtolower($heading[$i]) != strtolower($expected[$i])) {
				$valid = FALSE;
				break;
			}
		}
		return $valid;
	}

	protected function expectedWorkSheets() {
		return array('Items', 'Purchase Price', 'Sale Price', 'Suppliers');
	}


	function validateUpload( &$reader )
	{
		$expectedWorksheets = $this->expectedWorksheets();
		$sheetNames = $reader->getSheetNames();
		foreach ($expectedWorksheets as $key=>$expectedWorksheet) {
			if (!isset($sheetNames[$key]) || ($sheetNames[$key]!=$expectedWorksheet)) {
				error_log(date('Y-m-d H:i:s - ', time()).$this->language->get( 'error_sheet_count' )."\n",3,DIR_LOGS."error.txt");
				return FALSE;
			}
		}
		
		if (!$this->validateItems( $reader )) {
			error_log(date('Y-m-d H:i:s - ', time()).$this->language->get('error_items_header')."\n",3,DIR_LOGS."error.txt");
			return FALSE;
		}

		if (!$this->validatePurchasePrice( $reader )) {
			error_log(date('Y-m-d H:i:s - ', time()).$this->language->get('error_purchase_header')."\n",3,DIR_LOGS."error.txt");
			return FALSE;
		}

		if (!$this->validateSalePrice( $reader )) {
			error_log(date('Y-m-d H:i:s - ', time()).$this->language->get('error_sale_header')."\n",3,DIR_LOGS."error.txt");
			return FALSE;
		}

		if (!$this->validateSupplier( $reader )) {
			error_log(date('Y-m-d H:i:s - ', time()).$this->language->get('error_supplier_header')."\n",3,DIR_LOGS."error.txt");
			return FALSE;
		}
		
		return TRUE;
	}


	function clearCache() {
		$this->cache->delete('*');
	}


	function upload( $filename ) {
		// we use our own error handler
		global $registry;
		$registry = $this->registry;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');

		try {
			$database =& $this->db;
			$this->session->data['export_nochange'] = 1;

			// we use the PHPExcel package from http://phpexcel.codeplex.com/
			$cwd = getcwd();
			chdir( DIR_SYSTEM.'PHPExcel' );
			require_once( 'Classes/PHPExcel.php' );
			chdir( $cwd );

			// parse uploaded spreadsheet file
			$inputFileType = PHPExcel_IOFactory::identify($filename);
			$objReader = PHPExcel_IOFactory::createReader($inputFileType);
			$objReader->setReadDataOnly(true);
			$reader = $objReader->load($filename);

			// read the various worksheets and load them to the database
			$ok = $this->validateUpload( $reader );
			if (!$ok) {
				return FALSE;
			}
			$this->clearCache();
			$this->session->data['export_nochange'] = 0;
			
			$ok = $this->uploadItems( $reader, $database );
			if (!$ok) {
				return FALSE;
			}

			$ok = $this->uploadPurchasePrice( $reader, $database );
			if (!$ok) {
				return FALSE;
			}

			$ok = $this->uploadSalePrice( $reader, $database );
			if (!$ok) {
				return FALSE;
			}

			$ok = $this->uploadSupplier( $reader, $database );
			if (!$ok) {
				return FALSE;
			}
			
			return $ok;
		} catch (Exception $e) {
			$errstr = $e->getMessage();
			$errline = $e->getLine();
			$errfile = $e->getFile();
			$errno = $e->getCode();
			$this->session->data['export_error'] = array( 'errstr'=>$errstr, 'errno'=>$errno, 'errfile'=>$errfile, 'errline'=>$errline );
			if ($this->config->get('config_error_log')) {
				$this->log->write('PHP ' . get_class($e) . ':  ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
			}
			return FALSE;
		}
	}

	protected function setCell( &$worksheet, $row/*1-based*/, $col/*0-based*/, $val, $style=NULL ) {
		$worksheet->setCellValueByColumnAndRow( $col, $row, $val );
		if ($style) {
			$worksheet->getStyleByColumnAndRow($col,$row)->applyFromArray( $style );
		}
	}

	function populateItemsWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('item_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('name'),30)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('group_id'),3)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('purchase_code'),25)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('sale_code'),25)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('image'),12)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('unit'),10)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('status'),10)+1,$textFormat);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('language_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('description'),32)+1,$textFormat);
		
		// The item headings row
		$i = 1;
		$j = 0;
		$this->setCell( $worksheet, $i, $j++, 'item_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'name', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'group_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'purchase_code', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'sale_code', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'image', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'unit', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, "status", $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'language_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'description', $boxFormat );
		$worksheet->getRowDimension($i)->setRowHeight(30);
		
		// The actual items data
		$i += 1;
		$j = 0;
		
		$items = $this->getItems( $database, $languageId );
		foreach ($items as $row) {
			$worksheet->getRowDimension($i)->setRowHeight(26);
			$itemId = $row['item_id'];
			$this->setCell( $worksheet, $i, $j++, $itemId );
			$this->setCell( $worksheet, $i, $j++, html_entity_decode($row['name'],ENT_QUOTES,'UTF-8') );
			$this->setCell( $worksheet, $i, $j++, $row['group_id'] );
			$this->setCell( $worksheet, $i, $j++, $row['purchase_code'] );
			$this->setCell( $worksheet, $i, $j++, $row['sale_code'] );
			$this->setCell( $worksheet, $i, $j++, $row['image'] );
			$this->setCell( $worksheet, $i, $j++, $row['unit'] );
			$this->setCell( $worksheet, $i, $j++, ($row['status']==0) ? "false" : "true", $textFormat );
			$this->setCell( $worksheet, $i, $j++, $row['language_id'] );
			$this->setCell( $worksheet, $i, $j++, html_entity_decode($row['description'],ENT_QUOTES,'UTF-8'), $textFormat, TRUE );
			$i += 1;
			$j = 0;
		}
	}

	function populatePurchasePriceWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('item_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('supplier_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('price'),6)+1);
		
		// The item headings row
		$i = 1;
		$j = 0;
		$this->setCell( $worksheet, $i, $j++, 'item_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'supplier_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'price', $boxFormat );
		$worksheet->getRowDimension($i)->setRowHeight(30);
		
		// The actual items data
		$i += 1;
		$j = 0;
		
		$purchases = $this->getPurchasePrice( $database, $languageId );
		foreach ($purchases as $row) {
			$worksheet->getRowDimension($i)->setRowHeight(26);
			$itemId = $row['item_id'];
			$this->setCell( $worksheet, $i, $j++, $itemId );
			$this->setCell( $worksheet, $i, $j++, $row['supplier_id'] );
			$this->setCell( $worksheet, $i, $j++, $row['price'] );
			$i += 1;
			$j = 0;
		}
	}

	function populateSalePriceWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('item_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('type_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('quantity'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('price'),6)+1);
		
		// The item headings row
		$i = 1;
		$j = 0;
		$this->setCell( $worksheet, $i, $j++, 'item_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'type_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'quantity', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'price', $boxFormat );
		$worksheet->getRowDimension($i)->setRowHeight(30);
		
		// The actual items data
		$i += 1;
		$j = 0;
		
		$saleprices = $this->getSalePrice( $database, $languageId );
		foreach ($saleprices as $row) {
			$worksheet->getRowDimension($i)->setRowHeight(26);
			$itemId = $row['item_id'];
			$this->setCell( $worksheet, $i, $j++, $itemId );
			$this->setCell( $worksheet, $i, $j++, $row['type_id'] );
			$this->setCell( $worksheet, $i, $j++, $row['quantity'] );
			$this->setCell( $worksheet, $i, $j++, $row['price'] );
			$i += 1;
			$j = 0;
		}
	}

	function populateSupplierWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('supplier_id'),4)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('shortname'),30)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('name'),30)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('address'),30)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('tax_code'),10)+1);
		$worksheet->getColumnDimensionByColumn($j++)->setWidth(max(strlen('sort_order'),4)+1);
		
		// The item headings row
		$i = 1;
		$j = 0;
		$this->setCell( $worksheet, $i, $j++, 'supplier_id', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'shortname', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'name', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'address', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'tax_code', $boxFormat );
		$this->setCell( $worksheet, $i, $j++, 'sort_order', $boxFormat );
		$worksheet->getRowDimension($i)->setRowHeight(30);
		
		// The actual items data
		$i += 1;
		$j = 0;
		
		$suppliers = $this->getSupplier( $database, $languageId );
		foreach ($suppliers as $row) {
			$worksheet->getRowDimension($i)->setRowHeight(26);
			$supplierId = $row['supplier_id'];
			$this->setCell( $worksheet, $i, $j++, $supplierId );
			$this->setCell( $worksheet, $i, $j++, $row['short_name'] );
			$this->setCell( $worksheet, $i, $j++, $row['name'] );
			$this->setCell( $worksheet, $i, $j++, $row['address'] );
			$this->setCell( $worksheet, $i, $j++, $row['tax_code'] );
			$this->setCell( $worksheet, $i, $j++, $row['sort_order'] );
			$i += 1;
			$j = 0;
		}
	}



	protected function clearSpreadsheetCache() {
		$files = glob(DIR_CACHE . 'Spreadsheet_Excel_Writer' . '*');
		
		if ($files) {
			foreach ($files as $file) {
				if (file_exists($file)) {
					@unlink($file);
					clearstatcache();
				}
			}
		}
	}


	function download() {
		// we use our own error handler
		global $registry;
		$registry = $this->registry;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');

		// we use the PHPExcel package from http://phpexcel.codeplex.com/
		$cwd = getcwd();
		chdir( DIR_SYSTEM.'PHPExcel' );
		require_once( 'Classes/PHPExcel.php' );
		chdir( $cwd );

		try {
			// set appropriate timeout limit
			set_time_limit( 1800 );

			$database =& $this->db;
			$languageId = $this->getDefaultLanguageId($database);

			// create a new workbook
			$workbook = new PHPExcel();

			// set default font name and size
			$workbook->getDefaultStyle()->getFont()->setName('Arial');
			$workbook->getDefaultStyle()->getFont()->setSize(10);
			$workbook->getDefaultStyle()->getAlignment()->setIndent(1);

			// pre-define some commonly used styles
			$boxFormat = array(
				'font' => array(
					'name' => 'Arial',
					'size' => '10',
				),
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
					'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
					'wrap'       => false,
					'indent'     => 0
				)
			);
			$textFormat = array(
				'font' => array(
					'name' => 'Arial',
					'size' => '10',
				),
				'numberformat' => array(
					'code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT
				),
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
					'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
					'wrap'       => false,
					'indent'     => 0
				)
			);
			$priceFormat = array(
				'font' => array(
					'name' => 'Arial',
					'size' => '10',
				),
				'numberformat' => array(
					'code' => '######0.00'
				),
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
					'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
					'wrap'       => false,
					'indent'     => 0
				)
			);
			$weightFormat = array(
				'font' => array(
					'name' => 'Arial',
					'size' => '10',
				),
				'numberformat' => array(
					'code' => '##0.00'
				),
				'alignment' => array(
					'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
					'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
					'wrap'       => false,
					'indent'     => 0
				)
			);

			

			// creating the items worksheet
			$workbook->setActiveSheetIndex(0);
			$worksheet = $workbook->getActiveSheet();
			$worksheet->setTitle( 'Items' );
			$this->populateItemsWorksheet( $worksheet, $database, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
			$worksheet->freezePaneByColumnAndRow( 1, 2 );


			// creating the purchase price worksheet
			$workbook->createSheet();
			$workbook->setActiveSheetIndex(1);
			$worksheet = $workbook->getActiveSheet();
			$worksheet->setTitle( 'Purchase Price' );
			$this->populatePurchasePriceWorksheet( $worksheet, $database, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
			$worksheet->freezePaneByColumnAndRow( 1, 2 );

			// creating the sale price worksheet
			$workbook->createSheet();
			$workbook->setActiveSheetIndex(2);
			$worksheet = $workbook->getActiveSheet();
			$worksheet->setTitle( 'Sale Price' );
			$this->populateSalePriceWorksheet( $worksheet, $database, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
			$worksheet->freezePaneByColumnAndRow( 1, 2 );

			// creating the supplier worksheet
			$workbook->createSheet();
			$workbook->setActiveSheetIndex(3);
			$worksheet = $workbook->getActiveSheet();
			$worksheet->setTitle( 'Suppliers' );
			$this->populateSupplierWorksheet( $worksheet, $database, $languageId, $priceFormat, $boxFormat, $weightFormat, $textFormat );
			$worksheet->freezePaneByColumnAndRow( 1, 2 );

			$workbook->setActiveSheetIndex(0);

			// redirect output to client browser
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header('Content-Disposition: attachment;filename="backup_items.xlsx"');
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
			$objWriter->save('php://output');

			// Clear the spreadsheet caches
			$this->clearSpreadsheetCache();
			exit;

		} catch (Exception $e) {
			$errstr = $e->getMessage();
			$errline = $e->getLine();
			$errfile = $e->getFile();
			$errno = $e->getCode();
			$this->session->data['export_error'] = array( 'errstr'=>$errstr, 'errno'=>$errno, 'errfile'=>$errfile, 'errline'=>$errline );
			if ($this->config->get('config_error_log')) {
				$this->log->write('PHP ' . get_class($e) . ':  ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
			}
			return;
		}
	}

}
?>